﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.BasicList
{
    public partial class panel_Kosei : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        private SqlConnection myConnection2;
        private SqlConnection masterConnection;
        
        public panel_Kosei()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.myConnection2 = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.Kosei.DoubleClick += Kosei_DoubleClick;
            this.Kosei.PopupMenuShowing += Kosei_PopupMenuShowing;
            this.koseiContext.ItemClicked += koseiContext_ItemClicked;
        }

        private void callEditForm()
        {
            DataRow row = Kosei.GetFocusedDataRow();
            frm_Edit_Kosei editForm = new frm_Edit_Kosei();
            editForm.comp_id = row[1].ToString();
            editForm.part_no = row[2].ToString();
            if (editForm.LoadData())
            {                
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot edit the Kosei that imported from AS400.");
            }
        }

        void Kosei_DoubleClick(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        void koseiContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.koseiContext.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "importAS400Selection":
                    importDatabase();
                    break;
                case "importExcelSelection":
                    imporExceltDatabase();
                    break;
                case "addSelection":
                    frm_Add_Kosei add_form = new frm_Add_Kosei();
                    add_form.ShowDialog();
                    LoadData();
                    break;
                case "editSelection":
                    callEditForm();
                    break;
                case "deleteSelection":
                    deleteData();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void Kosei_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.koseiContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT * FROM [222_Kosei2]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridKosei.DataSource = sourceDataSet.Tables[0];
                getUpdateInfo();
            }
            else
            {
                MessageBox.Show("There is no data for Kosei");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("CompID", typeof(string));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("Quantity", typeof(string));
                newTable.Columns.Add("PartType", typeof(string));
                newTable.Columns.Add("Note", typeof(string));
                gridKosei.DataSource = newTable;
            }
            Kosei.Columns[0].Caption = "ID";
            Kosei.Columns[1].Caption = "Comp ID";
            Kosei.Columns[2].Caption = "Part No";
            Kosei.Columns[3].Caption = "Quantity";
            Kosei.Columns[4].Caption = "Part Type";
            Kosei.Columns[5].Caption = "Note";
            Kosei.Columns[0].Visible = false;
            Kosei.BestFitColumns();
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=1";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }

        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void importDatabase()
        {            
            string sql = null;
            SqlCommand cmd;
            try
            {
                masterConnection.Open();
                
                // Delete current database
                sql = "DELETE [rrc_database].dbo.[222_Kosei]";
                cmd = new SqlCommand(sql, masterConnection);
                cmd.CommandTimeout = 10000;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                // Import database from AS400
                sql = "INSERT INTO [rrc_database].dbo.[222_Kosei] (CompID, PartNo, Quantity, PartType)"
                    + " SELECT * FROM [VAS400Kosei]";
                cmd = new SqlCommand(sql, masterConnection);
                cmd.CommandTimeout = 10000;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                masterConnection.Close();

                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=1";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
                LoadData();
                MessageBox.Show("Database has been updated successfull.");
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
            }            
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_Kosei add_form = new frm_Add_Kosei();
            add_form.ShowDialog();
            LoadData();
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteData();
        }

        ///////////////////////////////////
        /// Delete selected rows
        //////////////////////////////////
        private void deleteData()
        {
            if (MessageBox.Show("Are you really to delete selected record now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {                
                int[] selected_row = Kosei.GetSelectedRows();
                myConnection.Open();
                int k = 0;
                foreach (int i in selected_row)
                {
                    bool exist = false;
                    try
                    {
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT * FROM [222_Kosei2] WHERE"
                            + " ID='" + Kosei.GetRowCellValue(i, "ID").ToString() + "'";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            exist = true;
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                    }
                    catch
                    {
                        exist = false;
                    }
                    if (exist)
                    {
                        try
                        {
                            string sql = "DELETE FROM [222_Kosei2] "
                                            + "WHERE ID='" + Kosei.GetRowCellValue(i, "ID").ToString() + "'";
                            SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();
                            k++;
                        }
                        catch
                        {

                        }
                    }
                }
                myConnection.Close();
                MessageBox.Show(k + " recoreds have been deleted.");
                LoadData();
            }
        }


        ////////////////////////////////////
        /// Update database From Excel
        ////////////////////////////////////
        private void imporExceltDatabase()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();

            bool have_data = false;

            importFile.Filter = "Excel 2007-2010 (.xlsx)|*.xlsx|Excel 97-2003 (.xls)|*.xls";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("SELECT [CompID],[PartNo],[Quantity],[Note]"
                        + " FROM [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }

            if (have_data)
            {

                DataSet customer = new DataSet();
                string sql = null;
                SqlDataAdapter adapter;
                DataTable kosei_local;
                try
                {
                    //Get Data From Local Database
                    myConnection.Open();
                    sql = "SELECT * FROM [222_Kosei2]";
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(customer, "222_Kosei2");
                    kosei_local = customer.Tables["222_Kosei2"];
                    int i = 0;
                    foreach (DataRow excel_row in excel_data.Rows)
                    {
                        if (!IsExistKosei(excel_row["CompID"].ToString(), excel_row["PartNo"].ToString().Trim()))
                        {
                            float quantity = 0;
                            if (float.TryParse(excel_row["Quantity"].ToString().Trim(), out quantity))
                            {
                                DataRow new_row = kosei_local.NewRow();
                                new_row["CompID"] = excel_row["CompID"].ToString().Trim();
                                new_row["PartNo"] = excel_row["PartNo"].ToString().Trim();
                                new_row["Quantity"] = quantity;
                                new_row["PartType"] = "P";
                                new_row["Note"] = excel_row["Note"].ToString().Trim();
                                kosei_local.Rows.Add(new_row);
                                i++;
                            }
                        }
                    }


                    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                    //Update to Local Database
                    adapter.Update(customer, "222_Kosei2");

                    adapter.Dispose();
                    cmd.Dispose();
                    myConnection.Close();

                    //Update History
                    myConnection.Open();
                    sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=1";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();

                    LoadData();

                    MessageBox.Show("Database has been updated successfull.\r\n " + i.ToString() + " records added");

                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot connect to AS400");
                }
            }
        }

        /////////////////////////////////////
        /// Check exist Kosei
        /////////////////////////////////////
        private bool IsExistKosei(string comp, string part)
        {
            bool ret = false;

            try
            {
                myConnection2.Open();
                string sql = "SELECT COUNT(CompID) FROM VKosei WHERE CompID='" + comp + "' AND PartNo='" + part + "'";
                SqlCommand cmd_sql2 = new SqlCommand(sql, myConnection2);
                SqlDataReader dataReader2 = cmd_sql2.ExecuteReader();
                if (dataReader2.Read())
                {
                    if (dataReader2[0] != DBNull.Value)
                    {
                        if (Int32.Parse(dataReader2[0].ToString()) > 0)
                        {
                            ret = true;
                        }
                    }
                }
                cmd_sql2.Dispose();
                myConnection2.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot check exist Kosei. Please try again.");
            }

            return ret;
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            imporExceltDatabase();
        }
    }
}
